using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;
using System.Text;

namespace PCSComSale.Order.DS
{
    public class SO_SaleOrderDetailDS
    {
        public SO_SaleOrderDetailDS()
        {
        }

        private const string THIS = "PCSComSale.Order.DS.SO_SaleOrderDetailDS";
        public void Add(object pobjObjectVO)
        {
            const string METHOD_NAME = THIS + ".Add()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                SO_SaleOrderDetailVO objObject = (SO_SaleOrderDetailVO)pobjObjectVO;
                StringBuilder strSql = new StringBuilder();
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand("", oconPCS);

                strSql.Append("INSERT INTO SO_SaleOrderDetail(");
                strSql.Append(SO_SaleOrderDetailTable.SALEORDERLINE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.AUTOCOMMIT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ORDERQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.UNITPRICE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.VATAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.TOTALAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.NETAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.CANCELREASONID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.VATPERCENT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.UMRATE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SHIPQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.BACKORDERQTY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.STOCKQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.PRODUCTID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.REASONID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.STOCKUMID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SELLINGUMID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.FGOODSCODE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.DUEDATE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.WOQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD + ")");
                strSql.Append("VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SALEORDERLINE_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SALEORDERLINE_FLD].Value = objObject.SaleOrderLine;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.AUTOCOMMIT_FLD, OleDbType.Boolean));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.AUTOCOMMIT_FLD].Value = objObject.AutoCommit;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.ORDERQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD].Value = objObject.OrderQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.UNITPRICE_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.UNITPRICE_FLD].Value = objObject.UnitPrice;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.VATAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.VATAMOUNT_FLD].Value = objObject.VATAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD].Value = objObject.ExportTaxAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD].Value = objObject.SpecialTaxAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.TOTALAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD].Value = objObject.TotalAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD].Value = objObject.DiscountAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.NETAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.NETAMOUNT_FLD].Value = objObject.NetAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD, OleDbType.WChar));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD].Value = objObject.ItemCustomerCode;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD, OleDbType.WChar));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD].Value = objObject.ItemCustomerRevision;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.CANCELREASONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.CANCELREASONID_FLD].Value = objObject.CancelReasonID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.VATPERCENT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.VATPERCENT_FLD].Value = objObject.VATPercent;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD].Value = objObject.ExportTaxPercent;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD].Value = objObject.SpecialTaxPercent;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.UMRATE_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.UMRATE_FLD].Value = objObject.UMRate;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SHIPQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SHIPQUANTITY_FLD].Value = objObject.ShipQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.BACKORDERQTY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.BACKORDERQTY_FLD].Value = objObject.BackOrderQty;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.STOCKQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.STOCKQUANTITY_FLD].Value = objObject.StockQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.PRODUCTID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.PRODUCTID_FLD].Value = objObject.ProductID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD].Value = objObject.ConvertedQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.REASONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.REASONID_FLD].Value = objObject.ReasonID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD].Value = objObject.SaleOrderMasterID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.STOCKUMID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.STOCKUMID_FLD].Value = objObject.StockUMID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SELLINGUMID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SELLINGUMID_FLD].Value = objObject.SellingUMID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.FGOODSCODE_FLD, OleDbType.WChar));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.FGOODSCODE_FLD].Value = objObject.FGoodCode;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.DUEDATE_FLD, OleDbType.DBTime));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.DUEDATE_FLD].Value = objObject.DueDate;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD].Value = objObject.ItemDescription;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.WOQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.WOQUANTITY_FLD].Value = objObject.WOQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD, OleDbType.Boolean));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD].Value = objObject.ShipCompleted;

                ocmdPCS.CommandText = strSql.ToString();
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void Delete(int pintID)
        {
            const string METHOD_NAME = THIS + ".Delete()";
            string strSql = String.Empty;
            strSql = "DELETE " + SO_SaleOrderDetailTable.TABLE_NAME + " WHERE  " + "SaleOrderDetailID" + "=" + pintID.ToString();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
                ocmdPCS = null;

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }


            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void DeleteSaleOrder(int pintID)
        {
            const string METHOD_NAME = THIS + ".Delete()";
            string strSql = String.Empty;
            strSql = "DELETE " + SO_SaleOrderDetailTable.TABLE_NAME + " WHERE  " + SO_SaleOrderMasterTable.SALEORDERMASTERID_FLD + "=" + pintID.ToString();
            strSql += "; DELETE " + SO_SaleOrderMasterTable.TABLE_NAME + " WHERE  " + SO_SaleOrderMasterTable.SALEORDERMASTERID_FLD + "=" + pintID.ToString();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
                ocmdPCS = null;

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }


            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
        public void Update(object pobjObjecVO)
        {
            const string METHOD_NAME = THIS + ".Update()";

            SO_SaleOrderDetailVO objObject = (SO_SaleOrderDetailVO)pobjObjecVO;


            //prepare value for parameters
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                strSql = "UPDATE SO_SaleOrderDetail SET "
                    + SO_SaleOrderDetailTable.SALEORDERLINE_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.AUTOCOMMIT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.ORDERQUANTITY_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.UNITPRICE_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.VATAMOUNT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.TOTALAMOUNT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.NETAMOUNT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.CANCELREASONID_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.VATPERCENT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.UMRATE_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.SHIPQUANTITY_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.BACKORDERQTY_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.STOCKQUANTITY_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.PRODUCTID_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.REASONID_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.STOCKUMID_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.SELLINGUMID_FLD + "=  ?" + ","
                    + SO_SaleOrderDetailTable.FGOODSCODE_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.DUEDATE_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.WOQUANTITY_FLD + "=   ?" + ","
                    + SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD + "=  ?"
                    + " WHERE " + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + "= ?";

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SALEORDERLINE_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SALEORDERLINE_FLD].Value = objObject.SaleOrderLine;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.AUTOCOMMIT_FLD, OleDbType.Boolean));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.AUTOCOMMIT_FLD].Value = objObject.AutoCommit;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.ORDERQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD].Value = objObject.OrderQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.UNITPRICE_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.UNITPRICE_FLD].Value = objObject.UnitPrice;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.VATAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.VATAMOUNT_FLD].Value = objObject.VATAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD].Value = objObject.ExportTaxAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD].Value = objObject.SpecialTaxAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.TOTALAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD].Value = objObject.TotalAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD].Value = objObject.DiscountAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.NETAMOUNT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.NETAMOUNT_FLD].Value = objObject.NetAmount;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD, OleDbType.WChar));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD].Value = objObject.ItemCustomerCode;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD, OleDbType.WChar));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD].Value = objObject.ItemCustomerRevision;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.CANCELREASONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.CANCELREASONID_FLD].Value = objObject.CancelReasonID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.VATPERCENT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.VATPERCENT_FLD].Value = objObject.VATPercent;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD].Value = objObject.ExportTaxPercent;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD].Value = objObject.SpecialTaxPercent;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.UMRATE_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.UMRATE_FLD].Value = objObject.UMRate;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SHIPQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SHIPQUANTITY_FLD].Value = objObject.ShipQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.BACKORDERQTY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.BACKORDERQTY_FLD].Value = objObject.BackOrderQty;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.STOCKQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.STOCKQUANTITY_FLD].Value = objObject.StockQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.PRODUCTID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.PRODUCTID_FLD].Value = objObject.ProductID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD].Value = objObject.ConvertedQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.REASONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.REASONID_FLD].Value = objObject.ReasonID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD].Value = objObject.SaleOrderMasterID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.STOCKUMID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.STOCKUMID_FLD].Value = objObject.StockUMID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SELLINGUMID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SELLINGUMID_FLD].Value = objObject.SellingUMID;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.FGOODSCODE_FLD, OleDbType.WChar));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.FGOODSCODE_FLD].Value = objObject.FGoodCode;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.DUEDATE_FLD, OleDbType.DBTime));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.DUEDATE_FLD].Value = objObject.DueDate;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD].Value = objObject.ItemDescription;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.WOQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.WOQUANTITY_FLD].Value = objObject.WOQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD, OleDbType.Boolean));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD].Value = objObject.ShipCompleted;

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].Value = objObject.SaleOrderDetailID;


                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }

        public DataTable GetSaleOrderTotalCommit(int pintSaleOrderMasterID)
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = " SELECT * FROM v_GetSaleOrderTotalInvCommit " +
                    " WHERE " + SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + "=" + pintSaleOrderMasterID;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, SO_SaleOrderDetailTable.TABLE_NAME);

                return dstPCS.Tables[0];
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }

        public DataTable GetAvgCommitCost(int pintSaleOrderMasterID)
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = " SELECT * FROM v_GetAvgCommitCost " +
                    " WHERE " + SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + "=" + pintSaleOrderMasterID;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, SO_SaleOrderDetailTable.TABLE_NAME);

                return dstPCS.Tables[0];
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }

        public DataSet List()
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                    + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + ","
                    + SO_SaleOrderDetailTable.SALEORDERLINE_FLD + ","
                    + SO_SaleOrderDetailTable.AUTOCOMMIT_FLD + ","
                    + SO_SaleOrderDetailTable.ORDERQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.UNITPRICE_FLD + ","
                    + SO_SaleOrderDetailTable.VATAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.TOTALAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.NETAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD + ","
                    + SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD + ","
                    + SO_SaleOrderDetailTable.CANCELREASONID_FLD + ","
                    + SO_SaleOrderDetailTable.VATPERCENT_FLD + ","
                    + SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD + ","
                    + SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD + ","
                    + SO_SaleOrderDetailTable.UMRATE_FLD + ","
                    + SO_SaleOrderDetailTable.SHIPQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.BACKORDERQTY_FLD + ","
                    + SO_SaleOrderDetailTable.STOCKQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.PRODUCTID_FLD + ","
                    + SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.REASONID_FLD + ","
                    + SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + ","
                    + SO_SaleOrderDetailTable.STOCKUMID_FLD + ","
                    + SO_SaleOrderDetailTable.SELLINGUMID_FLD + ","
                    + SO_SaleOrderDetailTable.FGOODSCODE_FLD + ","
                    + SO_SaleOrderDetailTable.DUEDATE_FLD + ","
                    + SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD + ","
                    + SO_SaleOrderDetailTable.WOQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD
                    + " FROM " + SO_SaleOrderDetailTable.TABLE_NAME;
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);

                odadPCS.Fill(dstPCS, SO_SaleOrderDetailTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }
        /// <summary>
        /// check schedule with commit quantity
        /// </summary>
        /// <param name="pintSOMasterID"></param>
        /// <returns></returns>
        /// <author>Trada</author>
        /// <date>Friday, April 28 2006</date>
        public DataSet GetSODetailDeliverySchedule(int pintSOMasterID)
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = " select D.SaleOrderDetailID, DL.deliveryquantity TotalDelivery,"
                    + " ISNULL((Select SUM(CommitQuantity) "
                    + " From dbo.SO_CommitInventoryDetail "
                    + " Where DeliveryScheduleID = DL.DeliveryScheduleID),0) as CommitQuantity"
                        + " from SO_DeliverySchedule DL "
                        + " inner join SO_SaleOrderDetail D on D.SaleOrderDetailID = DL.SaleOrderDetailID "
                        + " where D.SaleOrderDetailID in "
                        + " (Select SaleOrderDetailID from SO_DeliverySchedule)  "
                        + " and D.SaleOrderMasterID = " + pintSOMasterID.ToString();
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);

                odadPCS.Fill(dstPCS, SO_SaleOrderDetailTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }
        public DataSet List(int pintSaleOrderMasterID)
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                StringBuilder strSql = new StringBuilder();

                strSql.Append("SELECT ");
                strSql.Append(SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SALEORDERLINE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.FGOODSCODE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.DUEDATE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.TABLE_NAME + "." + SO_SaleOrderDetailTable.PRODUCTID_FLD + ",");
                strSql.Append(ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.CODE_FLD + ",");
                strSql.Append(ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.DESCRIPTION_FLD + ",");
                strSql.Append(ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.REVISION_FLD + ",");
                strSql.Append(" CA." + ITM_CategoryTable.CODE_FLD + " " + ITM_CategoryTable.TABLE_NAME + ITM_CategoryTable.CODE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.TABLE_NAME + "." + SO_SaleOrderDetailTable.ORDERQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.WOQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.TABLE_NAME + "." + SO_SaleOrderDetailTable.SELLINGUMID_FLD + ",");
                strSql.Append(MST_UnitOfMeasureTable.TABLE_NAME + "." + MST_UnitOfMeasureTable.CODE_FLD + " MST_UnitOfMeasureCode,");
                strSql.Append(SO_SaleOrderDetailTable.UNITPRICE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.VATPERCENT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.VATAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.TOTALAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.NETAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.TABLE_NAME + "." + SO_SaleOrderDetailTable.CANCELREASONID_FLD + ",");
                strSql.Append(SO_CancelReasonTable.TABLE_NAME + "." + SO_CancelReasonTable.CODE_FLD + " SO_CancelReasonCode,");
                strSql.Append(SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.AUTOCOMMIT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.UMRATE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SHIPQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.BACKORDERQTY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.STOCKQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.REASONID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.TABLE_NAME + "." + SO_SaleOrderDetailTable.STOCKUMID_FLD + ", ");
                strSql.Append("(SELECT Count (*) FROM " + SO_DeliveryScheduleTable.TABLE_NAME + " DS ");
                strSql.Append(" WHERE DS." + SO_DeliveryScheduleTable.SALEORDERDETAILID_FLD + " = ");
                strSql.Append(SO_SaleOrderDetailTable.TABLE_NAME + "." + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD);
                strSql.Append(") AS " + SO_DeliveryScheduleTable.DELIVERYNO_FLD);
                strSql.Append(" FROM " + SO_SaleOrderDetailTable.TABLE_NAME);

                strSql.Append(" INNER JOIN " + ITM_ProductTable.TABLE_NAME);
                strSql.Append(" ON " + SO_SaleOrderDetailTable.TABLE_NAME + "." + SO_SaleOrderDetailTable.PRODUCTID_FLD + " = ");
                strSql.Append(ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.PRODUCTID_FLD);

                strSql.Append(" LEFT JOIN " + MST_UnitOfMeasureTable.TABLE_NAME);
                strSql.Append(" ON " + MST_UnitOfMeasureTable.TABLE_NAME + "." + MST_UnitOfMeasureTable.UNITOFMEASUREID_FLD + " = ");
                strSql.Append(SO_SaleOrderDetailTable.TABLE_NAME + "." + SO_SaleOrderDetailTable.SELLINGUMID_FLD);

                strSql.Append(" LEFT JOIN " + SO_CancelReasonTable.TABLE_NAME);
                strSql.Append(" ON " + SO_CancelReasonTable.TABLE_NAME + "." + SO_CancelReasonTable.CANCELREASONID_FLD + " = ");
                strSql.Append(SO_SaleOrderDetailTable.TABLE_NAME + "." + SO_SaleOrderDetailTable.CANCELREASONID_FLD);

                strSql.Append(" LEFT JOIN " + ITM_CategoryTable.TABLE_NAME);
                strSql.Append(" CA ON CA" + "." + ITM_CategoryTable.CATEGORYID_FLD + " = ");
                strSql.Append(ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.CATEGORYID_FLD);

                strSql.Append(" WHERE " + SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + "=" + pintSaleOrderMasterID.ToString());
                strSql.Append(" ORDER BY " + SO_SaleOrderDetailTable.SALEORDERLINE_FLD);
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, SO_SaleOrderDetailTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }

        public DataSet List(string pstrSaleOrderCode)
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                    + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD
                    + " FROM " + SO_SaleOrderDetailTable.TABLE_NAME + " SODetail INNER JOIN " + SO_SaleOrderMasterTable.TABLE_NAME + " SOMaster "
                    + " ON " + "SODetail." + SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + " = SOMaster." + SO_SaleOrderMasterTable.SALEORDERMASTERID_FLD
                    + " WHERE SOMaster." + SO_SaleOrderMasterTable.CODE_FLD + " = '" + pstrSaleOrderCode.ToString() + "'";
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, SO_SaleOrderDetailTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
        public void UpdateDataSet(DataSet pData)
        {
            const string METHOD_NAME = THIS + ".UpdateDataSet()";
            string strSql;
            OleDbConnection oconPCS = null;
            OleDbCommandBuilder odcbPCS;
            OleDbDataAdapter odadPCS = new OleDbDataAdapter();

            try
            {
                strSql = "SELECT "
                    + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + ","
                    + SO_SaleOrderDetailTable.SALEORDERLINE_FLD + ","
                    + SO_SaleOrderDetailTable.AUTOCOMMIT_FLD + ","
                    + SO_SaleOrderDetailTable.ORDERQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.UNITPRICE_FLD + ","
                    + SO_SaleOrderDetailTable.VATAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.TOTALAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.NETAMOUNT_FLD + ","
                    + SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD + ","
                    + SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD + ","
                    + SO_SaleOrderDetailTable.CANCELREASONID_FLD + ","
                    + SO_SaleOrderDetailTable.VATPERCENT_FLD + ","
                    + SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD + ","
                    + SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD + ","
                    + SO_SaleOrderDetailTable.UMRATE_FLD + ","
                    + SO_SaleOrderDetailTable.SHIPQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.BACKORDERQTY_FLD + ","
                    + SO_SaleOrderDetailTable.STOCKQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.PRODUCTID_FLD + ","
                    + SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.REASONID_FLD + ","
                    + SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + ","
                    + SO_SaleOrderDetailTable.STOCKUMID_FLD + ","
                    + SO_SaleOrderDetailTable.SELLINGUMID_FLD + ","
                    + SO_SaleOrderDetailTable.FGOODSCODE_FLD + ","
                    + SO_SaleOrderDetailTable.DUEDATE_FLD + ","
                    + SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD + ","
                    + SO_SaleOrderDetailTable.WOQUANTITY_FLD + ","
                    + SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD
                    + "  FROM " + SO_SaleOrderDetailTable.TABLE_NAME;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                OleDbCommand cmdSelect = new OleDbCommand(strSql, oconPCS);
                //				cmdSelect.CommandTimeout = 10000;
                odadPCS.SelectCommand = cmdSelect;
                odcbPCS = new OleDbCommandBuilder(odadPCS);
                pData.EnforceConstraints = false;
                odadPCS.Update(pData, SO_SaleOrderDetailTable.TABLE_NAME);
            }
            catch (OleDbException ex)
            {
                if (ex.Errors.Count > 1)
                {
                    if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                        throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                    else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                        throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                    else
                        throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
                else
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataTable Search(int pintCCNID, DateTime pdtmFromDeliveryDate, DateTime pdtmToDeliveryDate, string pstrOrderNo, string pstrItem, string pstrRevision, string pstrDescription)
        {
            const string METHOD_NAME = THIS + ".List()";
            const string SQL_DATETIME_FORMAT = "yyyy-MM-dd HH:mm";

            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = "SELECT DS.DeliveryScheduleID, SOD.SaleOrderDetailID,SOM.SaleOrderMasterID,SOD.ProductID,SOD.SellingUMID,SOM.CCNID, \n"
                    + " SOM.ShipFromLocID,P.MasterLocationID, P.LocationID ITM_ProductLocationID, ISNULL(P.BinID,0) ITM_ProductBinID, PL.Code ITM_ProductLocationIDCode,PB.Code ITM_ProductBinIDCode, \n"
                    + " SOD.StockUMID, SOD.UMRate, \n"
                    + " SOM.CODE, SOD.SaleOrderLine,DS.Line,  DS.ScheduleDate, P.Code Item, P.Description, P.Revision, UM.Code UMCode, 0 RELEASEVALUE \n"
                    + " ,ML.Code MasterLocation,0 Bin, '' LocationID,'' MST_LocationCode,'' BinID, '' MST_BINCode,ISNULL(DS.DeliveryQuantity,0) DeliveryQuantity, 0.00 AVAILABLEQUANTITY, CIR.CustomerItemCode CusCode, CIR.CustomerItemModel CusName, SOM.Priority,'' RELEASE, SO_Gate.Code as SO_GateCode \n"

                    + " FROM SO_DeliverySchedule DS \n"
                    + " INNER JOIN SO_SaleOrderDetail SOD on SOD.Saleorderdetailid = DS.SaleOrderDetailID \n"
                    + " INNER JOIN SO_SaleOrderMaster SOM on SOM.Saleordermasterid = SOD.SaleOrderMasterID AND SOM.CCNID = " + pintCCNID.ToString() + " \n"
                    + " LEFT JOIN SO_CustomerItemRefDetail CIR ON CIR.ProductID = SOD.ProductID "
                    + " AND (SELECT PartyID FROM dbo.SO_CustomerItemRefMaster WHERE CustomerItemRefMasterID = CIR.CustomerItemRefMasterID) = SOM.PartyID "
                    + " INNER JOIN ITM_Product P ON SOD.ProductID = P.ProductID  \n"
                    + " INNER JOIN MST_UnitOfMeasure AS UM ON UM.UNITOFMEASUREID = SOD.SELLINGUMID  \n"
                    + " Left JOIN MST_Location AS PL ON P.LocationID = PL.LocationID  \n"
                    + " Left JOIN MST_Bin AS PB ON P.BinID = PB.BinID  \n"
                    + " LEFT JOIN SO_Gate ON SO_Gate.GateID = DS.GateID"
                    + " INNER JOIN MST_MasterLocation ML ON SOM.ShipFromLocID = ML.MasterLocationID"
                    + " AND DS.DeliveryScheduleID NOT IN (SELECT DeliveryScheduleID FROM SO_CommitInventoryDetail)";

                // TRANSFROM
                if (pdtmFromDeliveryDate > DateTime.MinValue)
                {
                    strSql += " AND DATEDIFF(minute, DS.ScheduleDate, '" + pdtmFromDeliveryDate.ToString(SQL_DATETIME_FORMAT) + "') <= 0  \n";
                }
                // TRANSTO
                if (pdtmToDeliveryDate > DateTime.MinValue)
                {
                    strSql += " AND DATEDIFF(minute, DS.ScheduleDate,'" + pdtmToDeliveryDate.ToString(SQL_DATETIME_FORMAT) + "') >= 0  \n";
                }
                // ORDERNO
                if (pstrOrderNo.Length > 0)
                {
                    pstrOrderNo = pstrOrderNo.Replace("'", "''");
                    strSql += " AND (SOM.Code = '" + pstrOrderNo + "') \n";
                }
                // Code
                if (pstrItem.Length > 0)
                {
                    pstrItem = pstrItem.Replace("'", "''");
                    strSql += " AND (P.Code = '" + pstrItem + "') \n";
                }
                // Revision
                if (pstrRevision.Length > 0)
                {
                    pstrRevision = pstrRevision.Replace("'", "''");
                    strSql += " AND (P.Revision = '" + pstrRevision + "') \n";
                }
                // Description
                if (pstrDescription.Length > 0)
                {
                    pstrDescription = pstrDescription.Replace("'", "''");
                    strSql += " AND (P.Description = '" + pstrDescription + "') \n";
                }
                //strSql += " ORDER BY SOM.CODE, SOD.SALEORDERLINE, DS.LINE \n";
                strSql += " ORDER BY DS.ScheduleDate, Item, P.Revision \n";

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, SO_SaleOrderDetailTable.TABLE_NAME);

                return dstPCS.Tables[0];
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
        public decimal GetOrderQuantity(int pintSODetail)
        {
            const string METHOD_NAME = THIS + ".GetOrderQuantity()";

            //prepare value for parameters
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                strSql = "SELECT ISNULL("
                    + SO_SaleOrderDetailTable.ORDERQUANTITY_FLD + ", 0)"
                    + " FROM " + SO_SaleOrderDetailTable.TABLE_NAME
                    + " WHERE " + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + "= ?";

                ocmdPCS.Parameters.Add(new OleDbParameter(SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].Value = pintSODetail;

                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                object objReturn = ocmdPCS.ExecuteScalar();
                if (objReturn != null)
                {
                    return decimal.Parse(objReturn.ToString());
                }
                else
                {
                    return 0;
                }
            }
            catch (OleDbException ex)
            {
                if (ex.Errors.Count > 0)
                {
                    if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                    {
                        throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                    }
                    else
                    {
                        throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                    }
                }
                else
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }

        ///// <summary>
        ///// Check the shipment of Item on the SOLine
        ///// </summary>
        ///// <param name="pintSODetail"></param>
        ///// <returns></returns>
        ///// <author>
        ///// Do Manh Tuan
        ///// 07-07-2005
        ///// </author>
        public bool IsRemainQuantityNotShip(int pintSODetail)
        {
            const string METHOD_NAME = THIS + ".GetOrderQuantity()";
            //prepare value for parameters
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(string.Empty, oconPCS);
                strSql.Append("SELECT (Select Sum(" + SO_CommitInventoryDetailTable.COMMITQUANTITY_FLD + ") FROM ");
                strSql.Append(SO_CommitInventoryDetailTable.TABLE_NAME + " CMDetail inner join " + SO_DeliveryScheduleTable.TABLE_NAME + " SODel on CMDetail.");
                strSql.Append(SO_CommitInventoryDetailTable.DELIVERYSCHEDULEID_FLD + " = SODel." + SO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD);
                strSql.Append(" inner join " + SO_SaleOrderDetailTable.TABLE_NAME + " SODetail on SODetail." + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD);
                strSql.Append(" = SODel." + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD);
                strSql.Append(" Where SODetail." + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + " = " + pintSODetail);
                strSql.Append(" ) - (Select sum(" + SO_SaleOrderDetailTable.ORDERQUANTITY_FLD + ") FROM " + SO_SaleOrderDetailTable.TABLE_NAME);
                strSql.Append(" Where SaleOrderDetailID = " + pintSODetail + ")");
                ocmdPCS.CommandText = strSql.ToString();
                ocmdPCS.Connection.Open();
                object objReturn = ocmdPCS.ExecuteScalar();
                if ((objReturn != null) && (objReturn != DBNull.Value))
                {
                    if (decimal.Parse(objReturn.ToString()) != 0)
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (OleDbException ex)
            {
                if (ex.Errors.Count > 0)
                {
                    if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                    {
                        throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                    }
                    else
                    {
                        throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                    }
                }
                else
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
            return true;
        }

        #region // HACK: DuongNA 2005-10-21
        //**************************************************************************              
        ///    <Description>
        ///       This method uses to Search SO Release 
        ///    </Description>
        ///    <Inputs>
        ///            
        ///    </Inputs>
        ///    <Outputs>
        ///       
        ///    </Outputs>
        ///    <Returns>
        ///       
        ///    </Returns>
        ///    <Authors>
        ///       Nguyen An Duong
        ///    </Authors>
        ///    <History>
        ///       Friday, October 21, 2005
        ///    </History>
        ///    <Notes>
        ///    </Notes>
        //**************************************************************************
        public decimal GetUMRateForProduct(int pintProductID, int pintSellingUMID)
        {
            const string METHOD_NAME = THIS + ".GetUMRateForProduct()";
            DataSet dstPCS = new DataSet();

            OleDbDataReader odrPCS = null;
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                strSql = "SELECT "
                    + MST_UMRateTable.SCALE_FLD
                    + " FROM " + MST_UMRateTable.TABLE_NAME
                    + " INNER JOIN " + ITM_ProductTable.TABLE_NAME
                    + " ON " + MST_UMRateTable.UNITOFMEASUREINID_FLD + "=" + ITM_ProductTable.STOCKUMID_FLD
                    + " WHERE " + ITM_ProductTable.PRODUCTID_FLD + "=" + pintProductID
                    + " AND " + MST_UMRateTable.UNITOFMEASUREOUTID_FLD + "=" + pintSellingUMID;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                odrPCS = ocmdPCS.ExecuteReader();

                decimal decScale = 0;

                while (odrPCS.Read())
                {
                    decScale = Decimal.Parse(odrPCS[MST_UMRateTable.SCALE_FLD].ToString().Trim());
                }
                return decScale;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        #endregion // END: DuongNA 2005-10-21

        public void UpdateDataSetForImport(DataSet pData, int intMasterID)
        {
            const string METHOD_NAME = THIS + ".UpdateDataSet()";
            StringBuilder strSql = new StringBuilder();
            OleDbConnection oconPCS = null;
            OleDbCommandBuilder odcbPCS;
            OleDbDataAdapter odadPCS = new OleDbDataAdapter();

            try
            {
                strSql.Append("SELECT ");
                strSql.Append(SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SALEORDERLINE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.AUTOCOMMIT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ORDERQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.UNITPRICE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.VATAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.TOTALAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.NETAMOUNT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.CANCELREASONID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.VATPERCENT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.UMRATE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SHIPQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.BACKORDERQTY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.STOCKQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.PRODUCTID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.REASONID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.STOCKUMID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SELLINGUMID_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.FGOODSCODE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.DUEDATE_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.WOQUANTITY_FLD + ",");
                strSql.Append(SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD);
                strSql.Append("  FROM " + SO_SaleOrderDetailTable.TABLE_NAME);
                strSql.Append(" WHERE " + SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD + "=" + intMasterID.ToString());

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                odadPCS.SelectCommand = new OleDbCommand(strSql.ToString(), oconPCS);
                odcbPCS = new OleDbCommandBuilder(odadPCS);
                pData.EnforceConstraints = false;
                odadPCS.Update(pData, SO_SaleOrderDetailTable.TABLE_NAME);
                pData.Tables[SO_SaleOrderDetailTable.TABLE_NAME].Clear();
                odadPCS.Fill(pData.Tables[SO_SaleOrderDetailTable.TABLE_NAME]);
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }
            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public decimal GetUnitPriceDefualt(int pProductID, int pCustomerID)
        {
            const string METHOD_NAME = THIS + ".GetOrderQuantity()";
            //prepare value for parameters
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(string.Empty, oconPCS);
                strSql.Append("SELECT CASE (");
                strSql.Append("	SELECT Count(*) FROM SO_CustomerItemRefDetail CIR");
                strSql.Append("	Inner Join SO_CustomerItemRefMaster CIM ON CIR.CustomerItemRefMasterID = CIM.CustomerItemRefMasterID");
                strSql.Append("	AND ProductID = " + pProductID + " AND PartyID = " + pCustomerID + ") WHEN 0 THEN ");
                strSql.Append("		(Select ISNULL(ListPrice,0) From Itm_Product Where ProductID = " + pProductID + ")");
                strSql.Append(" ELSE");
                strSql.Append("		(SELECT UnitPrice FROM SO_CustomerItemRefDetail CIR");
                strSql.Append("		Inner Join SO_CustomerItemRefMaster CIM ON CIR.CustomerItemRefMasterID = CIM.CustomerItemRefMasterID");
                strSql.Append("		AND ProductID = " + pProductID + " AND PartyID = " + pCustomerID + ")");
                strSql.Append(" END ");

                ocmdPCS.CommandText = strSql.ToString();
                ocmdPCS.Connection.Open();
                object objReturn = ocmdPCS.ExecuteScalar();
                if ((objReturn != null) && (objReturn != DBNull.Value))
                {
                    return Convert.ToDecimal(objReturn);
                }
                else
                {
                    return 0;
                }
            }
            catch (OleDbException ex)
            {
                if (ex.Errors.Count > 0)
                {
                    if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                    {
                        throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                    }
                    else
                    {
                        throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                    }
                }
                else
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        ///    <Description>
        ///       This method uses to get list item detail with UnitPrice change
        ///    </Description>
        ///    <Inputs>
        ///        DataSet       
        ///    </Inputs>
        ///    <Outputs>
        ///       
        ///    </Outputs>
        ///    <Returns>
        ///       
        ///    </Returns>
        ///    <Authors>
        ///       CanhNv
        ///    </Authors>
        ///    <History>
        ///       18/04/2007
        ///    </History>
        ///    <Notes>
        ///    </Notes>
        public DataSet GetListDetail(int printSaleOrderMasterId, int printPartyID)
        {
            const string METHOD_NAME = THIS + ".GetListDetail()";
            DataSet dstPCS = new DataSet();

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                //Todo strSql
                strSql.Append("SELECT CIRD.UnitPrice UnitPriceCustomerIRD, ");
                strSql.Append("SOD." + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.SALEORDERLINE_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.AUTOCOMMIT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.ORDERQUANTITY_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.UNITPRICE_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.VATAMOUNT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.TOTALAMOUNT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.NETAMOUNT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.CANCELREASONID_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.VATPERCENT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.UMRATE_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.SHIPQUANTITY_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.BACKORDERQTY_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.STOCKQUANTITY_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.PRODUCTID_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.REASONID_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.STOCKUMID_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.SELLINGUMID_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.FGOODSCODE_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.DUEDATE_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD);
                strSql.Append(",SOD." + SO_SaleOrderDetailTable.WOQUANTITY_FLD);
                strSql.Append("SOD." + SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD);
                strSql.Append(" FROM SO_SaleOrderDetail SOD");
                strSql.Append(" INNER JOIN SO_CustomerItemRefDetail CIRD ON SOD.Productid = CIRD.ProductID");
                strSql.Append(" WHERE CIRD.CustomerItemRefMasterID = ");
                strSql.Append(printPartyID);
                strSql.Append(" AND CIRD.Unitprice <> 0");
                strSql.Append(" AND SOD.SaleOrdermasterid = ");
                strSql.Append(printSaleOrderMasterId);
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();
                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, SO_SaleOrderDetailTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet ListDetailByFGoodsCode(string[] paraFGoodsCode, int[] pintSaleOrderdetailID)
        {
            const string METHOD_NAME = THIS + ".ListDetailByFGoodsCode()";
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                bool bcheck = true;
                StringBuilder strCondition = new StringBuilder();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT ");
                strSql.Append(SO_SaleOrderDetailTable.FGOODSCODE_FLD);
                strSql.Append(" FROM ");
                strSql.Append(SO_SaleOrderDetailTable.TABLE_NAME);
                if (paraFGoodsCode.Length > 0)
                    for (int i = 0; i < paraFGoodsCode.Length; i++)
                        if (paraFGoodsCode[i] != null && paraFGoodsCode[i] != string.Empty)
                        {
                            if (strCondition.Length > 0) strCondition.Append(" OR ");
                            strCondition.Append("(" + SO_SaleOrderDetailTable.FGOODSCODE_FLD + " = '" + paraFGoodsCode[i].ToString().Trim() + "'");
                            if (pintSaleOrderdetailID[i] > 0)
                                strCondition.Append(" AND " + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + " <> " + pintSaleOrderdetailID[i].ToString().Trim());
                            strCondition.Append(")");
                        }
                if (strCondition.Length > 0) strSql.Append(" WHERE " + strCondition);
                else strSql.Append(" WHERE 1=-1" );
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, SO_SaleOrderDetailTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
    }
}